package modele.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import db.MySQLSingleton;

import modele.beanEntity.User;


public class UserMapper {
	
	private Connection db;
	
	public UserMapper() throws Exception{
		this.db=MySQLSingleton.getInstance();
	}
	
	public User loginUser(String login,String password) throws SQLException{
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM users WHERE login=? and pwd=?");
		ps.setString(1, login);
		ps.setString(2, password);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			User u = new User(rs.getInt("idusers"),rs.getString("firstname"),
					rs.getString("lastname"),rs.getInt("age"),rs.getString("email"),
					rs.getString("login"),rs.getString("pwd"),rs.getTimestamp("lasteconnectiondate"),
					rs.getInt("lastconnectionduration"),rs.getString("role"),rs.getString("other1"),
					rs.getString("other2"),rs.getString("other3"),rs.getString("img"));
			rs.close();
			ps.close();
			return u;
		}
		
		rs.close();
		ps.close();
		
		return null;
	}
	
	public User getUser(int id) throws SQLException{
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM users WHERE idusers= ?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			User u = new User(rs.getInt("idusers"),rs.getString("firstname"),
					rs.getString("lastname"),rs.getInt("age"),rs.getString("email"),
					rs.getString("login"),rs.getString("pwd"),rs.getTimestamp("lasteconnectiondate"),
					rs.getInt("lastconnectionduration"),rs.getString("role"),rs.getString("other1"),
					rs.getString("other2"),rs.getString("other3"),rs.getString("img"));
			rs.close();
			ps.close();
			return u;
		}
		
		rs.close();
		ps.close();
		
		return null;
	}
	
	public List<User> getUserList() throws SQLException{
		Statement s=this.db.createStatement();
		ResultSet rs = s.executeQuery("SELECT * FROM users");
		List<User> l=new ArrayList<User>();
		while(rs.next()){
			l.add(new User(rs.getInt("idusers"),rs.getString("firstname"),
					rs.getString("lastname"),rs.getInt("age"),rs.getString("email"),
					rs.getString("login"),rs.getString("pwd"),rs.getTimestamp("lasteconnectiondate"),
					rs.getInt("lastconnectionduration"),rs.getString("role"),rs.getString("other1"),
					rs.getString("other2"),rs.getString("other3"),rs.getString("img")));
		}
		rs.close();
		s.close();
		return l;
	}
	
	public boolean createUser(String fname,String lname,int age,String email,String login,String pwd,
			Timestamp lastConnection,int lastConnectionDuration,String role, String other1,String other2,
			String other3,String img) throws SQLException{
		
		PreparedStatement ps = 
				this.db.prepareStatement("INSERT INTO users(firstname,lastname,age,email,login," +
						"pwd,lasteconnectiondate,lastconnectionduration,role,other1,other2,other3,img) " +
				"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)");
		ps.setString(1, fname);
		ps.setString(2, lname);
		ps.setInt(3, age);
		ps.setString(4, email);
		ps.setString(5, login);
		ps.setString(6, pwd);
		ps.setTimestamp(7, lastConnection);
		ps.setInt(8, lastConnectionDuration);
		ps.setString(9, role);
		ps.setString(10, other1);
		ps.setString(11, other2);
		ps.setString(12, other3);
		ps.setString(13, img);
		
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
		
	}
	
	public boolean createUser(User u) throws SQLException{
		return this.createUser(u.getFirstName(), u.getLastName(), u.getAge(), u.getEmail(), u.getLogin(), 
				u.getPassword(), u.getLastConnection(), u.getLastConnectionDuration(), u.getRole(), u.getOther1(),
				u.getOther2(), u.getOther3(), u.getImg());
	}
	
	public boolean updateUser(int id,String fname,String lname,int age,String email,String login,String pwd,
			Timestamp lastConnection,int lastConnectionDuration,String role, String other1,String other2,
			String other3,String img) throws SQLException{
		
		PreparedStatement ps = 
				this.db.prepareStatement("UPDATE users SET firstname = ?,lastname = ?,age=?,email=?,login=?," +
						"pwd = ?,lasteconnectiondate = ?,lastconnectionduration = ?,role =? ,other1= ?,other2 = ?,other3 =?," +
						"img =? WHERE idusers=?");
		ps.setString(1, fname);
		ps.setString(2, lname);
		ps.setInt(3, age);
		ps.setString(4, email);
		ps.setString(5, login);
		ps.setString(6, pwd);
		ps.setTimestamp(7, lastConnection);
		ps.setInt(8, lastConnectionDuration);
		ps.setString(9, role);
		ps.setString(10, other1);
		ps.setString(11, other2);
		ps.setString(12, other3);
		ps.setString(13, img);
		ps.setInt(14, id);
		
		if(ps.executeUpdate()== 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean updateUser(User u) throws SQLException{
		return this.updateUser(u.getId(),u.getFirstName(), u.getLastName(), u.getAge(), u.getEmail(), u.getLogin(), 
				u.getPassword(), u.getLastConnection(), u.getLastConnectionDuration(), u.getRole(), u.getOther1(),
				u.getOther2(), u.getOther3(), u.getImg());
	}
	
	public boolean deleteUser(int id) throws SQLException{
		PreparedStatement ps = this.db.prepareStatement("DELETE FROM users WHERE idusers= ?");
		ps.setInt(1,id);
		if(ps.executeUpdate() == 0){
			ps.close();
			return false;
		}
		ps.close();
		return true;
	}
	
	public boolean deleteUser(User u) throws SQLException{
		return this.deleteUser(u.getId());
	}
	
	public boolean loginExists(String login) throws SQLException{
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM users WHERE login=?");
		ps.setString(1, login);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			rs.close();
			ps.close();
			return true;
		}
		
		rs.close();
		ps.close();
		
		return false;
	}
	
	public boolean userExists(int id) throws SQLException{
		PreparedStatement ps =this.db.prepareStatement("SELECT * FROM users WHERE idusers=?");
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		if(rs.next()){
			rs.close();
			ps.close();
			return true;
		}
		
		rs.close();
		ps.close();
		
		return false;
	}
	
}
